package cn.edu.tsinghua.cic.ccss.bo.impl;

import java.util.ArrayList;
import java.util.List;

import javax.annotation.Resource;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.forten.core.dao.rdb.hibernate.HibernateDao;
import org.forten.core.dao.rdb.hibernate.QueryInfo;
import org.forten.core.dao.rdb.query.hql.expression.Between;
import org.forten.core.dao.rdb.query.hql.expression.Expression;
import org.forten.core.dao.rdb.query.hql.expression.Like;
import org.forten.core.dao.rdb.query.hql.order.Order;
import org.forten.core.dao.rdb.query.hql.statement.OrderBy;
import org.forten.core.dao.rdb.query.hql.statement.Select;
import org.forten.core.dao.rdb.query.parameter.Param;
import org.forten.core.dao.rdb.query.parameter.Type;
import org.forten.core.ro.DtoResultWithPageInfo;
import org.forten.core.ro.PageInfo;
import org.forten.utils.collection.CollectionUtil;
import org.forten.utils.common.DateUtil;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import cn.edu.tsinghua.cic.ccss.bo.CourseStatisticsBo;
import cn.edu.tsinghua.cic.ccss.bo.SystemSettingBo;
import cn.edu.tsinghua.cic.ccss.dto.CourseStatisticsDto;
import cn.edu.tsinghua.cic.ccss.dto.CourseStatisticsDto4Export;
import cn.edu.tsinghua.cic.ccss.dto.CourseTotalStatisticsDto;
import cn.edu.tsinghua.cic.ccss.entity.Course;
import cn.edu.tsinghua.cic.ccss.qo.CourseStatisticsQo;
@Service("courseStatisticsBo")
public class CourseStatisticsBoImpl implements CourseStatisticsBo{

	@Resource(name = "hibernateDao")
	private HibernateDao hibernateDao;
	@Resource(name = "systemSettingBo")
	private SystemSettingBo systemSettingBo;
	
	@Override
	public void insertTotalNumsIntoExcel(Workbook wb,
			CourseTotalStatisticsDto totalDto) {
		Sheet sheet = wb.getSheetAt(0);
		int lastRowNum = sheet.getLastRowNum();
		Row row_title = sheet.createRow(lastRowNum +1);
		Cell cell_title = row_title.createCell(0);
		cell_title.setCellValue("总计");
		Row row_content = sheet.createRow(lastRowNum + 2);
		Cell cell_content1 = row_content.createCell(0);
		Cell cell_content2 = row_content.createCell(2);
		Cell cell_content3 = row_content.createCell(3);
		Cell cell_content4 = row_content.createCell(4);
		Cell cell_content5 = row_content.createCell(5);
		Cell cell_content6 = row_content.createCell(6);
		cell_content1.setCellValue(totalDto.getTotalCourseNum());
		cell_content2.setCellValue(totalDto.getTotalCreditNum());
		cell_content3.setCellValue(totalDto.getTotalStuNum());
		cell_content4.setCellValue(totalDto.getTotalActualStuNum());
		cell_content5.setCellValue(totalDto.getTotalChosenCreditNum());
		cell_content6.setCellValue(totalDto.getTotalActualChoseCreditNum());
		
	}
	
	@Override
	@Transactional
	public List<CourseStatisticsDto4Export> export(CourseStatisticsQo qo) {
		QueryInfo courseQi = getQueryInfoForStatistics(qo);
		List<Course> courseList = hibernateDao.findEntities(courseQi);
		
		if(CollectionUtil.isEmpty(courseList)){
			return new ArrayList<CourseStatisticsDto4Export>();
		}
		List<CourseStatisticsDto4Export> dtoResult = new 
				ArrayList<CourseStatisticsDto4Export>(courseList.size()+1);
		for(Course course : courseList){
			dtoResult.add(getCourseStatisticsDto4Export(course));
		}
		return dtoResult;
	}
	
	@Override
	@Transactional
	public DtoResultWithPageInfo<CourseStatisticsDto> queryCourseStatisticsByQo(
			CourseStatisticsQo qo,int page) {
		
		QueryInfo courseQi = getQueryInfoForStatistics(qo);
		courseQi.setSelect(Select.getCountSelect("id"));
		long total = hibernateDao.getCount(courseQi);
		PageInfo pi = PageInfo.getInstance(page,
				systemSettingBo.getIntValue("DEFAULT_PAGE_SIZE"), total);
		
		if(total == 0)
			return new DtoResultWithPageInfo<CourseStatisticsDto>(
					new ArrayList<CourseStatisticsDto>(),null);
		courseQi.setPage(true);
		courseQi.setFirstResult(pi.getFirstResultNum());
		courseQi.setMaxResult(pi.getPageSize());
		courseQi.setSelect(null);
		courseQi.setOrderby(OrderBy.getOrderBy(Order.desc("teachTime")));
		List<Course> courseList = hibernateDao.findEntities(courseQi);
		
		if(courseList.size() == 0){
			
			return new DtoResultWithPageInfo<CourseStatisticsDto>(
					new ArrayList<CourseStatisticsDto>(courseList.size()),null);
		} else {
			
			List<CourseStatisticsDto> dtos = new ArrayList<CourseStatisticsDto>(courseList.size());
			for(Course course:courseList){
				dtos.add(getCourseStatisticsDto(course));
			}
			
			return new DtoResultWithPageInfo<CourseStatisticsDto>(dtos,pi);
		}
		
	}
	
	@Override
	@Transactional
	public CourseTotalStatisticsDto queryCourseTotalStatistics(
			CourseStatisticsQo qo) {
		QueryInfo courseQi =getQueryInfoForStatistics(qo);
		List<Course> courseList = hibernateDao.findEntities(courseQi);
		CourseTotalStatisticsDto totalDto = new CourseTotalStatisticsDto();
		
		if(courseList.size() == 0) {
			totalDto.setTotalActualChoseCreditNum(0);
			totalDto.setTotalActualStuNum(0);
			totalDto.setTotalChosenCreditNum(0);
			totalDto.setTotalCourseNum(0);
			totalDto.setTotalCreditNum(0);
			totalDto.setTotalStuNum(0);
			return totalDto;
		}
		totalDto.setTotalCourseNum(courseList.size());
		//总数数组
		long[] totalNums = {0,0,0,0,0};
		for(Course course : courseList){
			getTotalNums(course,totalNums);
		}
		totalDto.setTotalCreditNum(totalNums[0]);
		totalDto.setTotalStuNum(totalNums[1]);
		totalDto.setTotalActualStuNum(totalNums[2]);
		totalDto.setTotalChosenCreditNum(totalNums[3]);
		totalDto.setTotalActualChoseCreditNum(totalNums[4]);
		return totalDto;
	}
	
	private void getTotalNums(Course course,long[] totalNums) {
		String hql_dropNum = "select count(id) From CourseCanceled where courseId = :courseId";
		String hql_attendenceNum = "select count(id) From CourseSelection where courseId = :courseId and state = :state";
		
		long dropNum = hibernateDao.getCount(hql_dropNum, 
				Param.getCommonParam("courseId", course.getId(), Type.LONG));
		long attendenceNum = hibernateDao.getCount(hql_attendenceNum, 
				Param.getCommonParam("courseId", course.getId(), Type.LONG),
				Param.getCommonParam("state", "0", Type.STRING));
		totalNums[0] += course.getCredit();
		totalNums[1] += course.getClassChosenNum() + dropNum;
		totalNums[2] += course.getClassChosenNum();
		totalNums[3] += course.getClassChosenNum() * course.getCredit();
		totalNums[4] += attendenceNum * course.getCredit();
	}

	/**
	 * 通过Qo查询课程信息
	 * */
	private QueryInfo getQueryInfoForStatistics(CourseStatisticsQo qo){
		
		QueryInfo courseQi = new QueryInfo(Course.class);
		// 获取课程信息begin
		Between betweenTime = null;
		Expression courseName = null;
		if(!"".equals(qo.getStart()) && qo.getStart()!=null && "".equals(qo.getEnd())){
			Param startParam = Param.getCommonParam("start", qo.getStart() + "0000",
					Type.STRING);
			Param endParam = Param.getCommonParam("end",
					DateUtil.convertDateToString(DateUtil.currentTime(),
							"yyyyMMdd2359"), Type.STRING);
			startParam.setPrefix("to_date(");
			endParam.setPrefix("to_date(");
			startParam.setPostfix(",'yyyyMMddHH24MI')");
			endParam.setPostfix(",'yyyyMMddHH24MI')");
			betweenTime = Between.between("teachTime", startParam, endParam);
			
		} else if (qo.getStart() != null && qo.getEnd() != null && !"".equals(qo.getStart())
				&& !"".equals(qo.getEnd())) {
			Param startParam = Param.getCommonParam("start", qo.getStart() + " 00:00",
					Type.STRING);
			Param endParam = Param.getCommonParam("end", qo.getEnd() + " 23:59",
					Type.STRING);
			startParam.setPrefix("to_date(");
			endParam.setPrefix("to_date(");
			startParam.setPostfix(",'yyyyMMdd HH24:MI')");
			endParam.setPostfix(",'yyyyMMdd HH24:MI')");
			betweenTime = Between.between("teachTime", startParam, endParam);
		} else if(!"".equals(qo.getCourseName()) && qo.getCourseName() != null){
			courseName = Like.like("name", 
					Param.getDefaultVarcharParamForLike("courseName", qo.getCourseName()));
		}
		
		if(betweenTime != null) {
			courseQi.addExpressionsToWhere(betweenTime);
		}
		if(courseName != null) {
			courseQi.addExpressionsToWhere(courseName);
		}
		return courseQi;
		
	}
	
	/**
	 * 通过Course信息提取统计信息
	 * */
	private CourseStatisticsDto getCourseStatisticsDto(Course course){
		
 		CourseStatisticsDto dto = new CourseStatisticsDto();
		
		String hql_dropNum = "select count(id) From CourseCanceled where courseId = :courseId";
		String hql_attendenceNum = "select count(id) From CourseSelection where courseId = :courseId and state = :state";
		
		long dropNum = hibernateDao.getCount(hql_dropNum, 
				Param.getCommonParam("courseId", course.getId(), Type.LONG));
		long attendenceNum = hibernateDao.getCount(hql_attendenceNum, 
				Param.getCommonParam("courseId", course.getId(), Type.LONG),
				Param.getCommonParam("state", "0", Type.STRING));
		//固定数据
		dto.setCourseName(course.getName());
		dto.setTeachTime(course.getTeachTime());
		dto.setCredit(course.getCredit());
		dto.setClassChosenNum(course.getClassChosenNum());
		dto.setTotalCredit(course.getClassChosenNum() * course.getCredit());
		//
		dto.setActualCredit(attendenceNum * course.getCredit());
		dto.setClassChosenTotalNum(course.getClassChosenNum() + dropNum);
		return dto;
	}

private CourseStatisticsDto4Export getCourseStatisticsDto4Export(Course course){
		
 		CourseStatisticsDto4Export dto = new CourseStatisticsDto4Export();
		
		String hql_dropNum = "select count(id) From CourseCanceled where courseId = :courseId";
		String hql_attendenceNum = "select count(id) From CourseSelection where courseId = :courseId and state = :state";
		
		long dropNum = hibernateDao.getCount(hql_dropNum, 
				Param.getCommonParam("courseId", course.getId(), Type.LONG));
		long attendenceNum = hibernateDao.getCount(hql_attendenceNum, 
				Param.getCommonParam("courseId", course.getId(), Type.LONG),
				Param.getCommonParam("state", "0", Type.STRING));
		//固定数据
		dto.setCourseName(course.getName());
		dto.setTeachTime(course.getTeachTime());
		dto.setCredit(course.getCredit());
		dto.setClassChosenNum(course.getClassChosenNum());
		dto.setTotalCredit(course.getClassChosenNum() * course.getCredit());
		//
		dto.setActualCredit(attendenceNum * course.getCredit());
		dto.setClassChosenTotalNum(course.getClassChosenNum() + dropNum);
		return dto;
	}

}
